# -*- coding: utf-8 -*-
# @Project: mp-service
# @Author: YiWen
# @File name: sql_generate
# @Create time: 2021/10/18 10:31
import datetime
import re

from typing import Optional


class PagerBase:
    page: int = 1
    limit: int = 10


class CommonError(Exception):
    def __init__(self, message: str = 'error'):
        self.message = message

    def __str__(self):
        return self.message



def sql_or(*args):
    return ' OR '.join(args)


def sql_and(*args):
    args = [i for i in args if i]
    return ' AND '.join(args)


class or_q:

    def __init__(self, other):
        self.q = 'or'
        self.other = other

class and_q:
    def __init__(self, other):
        self.q = 'and'
        self.other = other

class not_q:
    def __init__(self, other):
        self.q = 'not'
        self.other = other



def null_field_dispose(data: dict):
    for k in data:
        if data[k] is None:
            data[k] = ''


def time_filed_dispose(data: dict):
    edu = str(data.get('edu'))
    exp = str(data.get('exp'))

    date_str = {key: str(val) for key, val in data.items()
                if isinstance(data.get(key), (datetime.datetime, datetime.date))}
    data.update(date_str)


class BaseSql(object):
    table: str = None

    def __init__(self, db, table: str = None):
        self.parma_list = []
        self._select_args = '*'
        self._limit_sql = ''
        self._order_by_sql = ''
        self._where_sql = ''
        self.match_sql = ''
        self._join_sql = ''
        self._group_by = ''
        self.pager: Optional[PagerBase] = None
        self._match_sql = ()
        if table:
            self.table = table
        if not self.table:
            table = self.__class__.__name__
            self.table = '_'.join(re.findall('[A-Z][^A-Z]*', table)).lower()
        self.db = db

    def inner_join(self, table: str, where: str):
        self._join_sql += f'inner join {table} on {where} '
        return self

    def left_join(self, table: str, where: str):
        self._join_sql += f'left join {table} on {where} '
        return self

    def query(self, *args):
        self._select_args = ','.join([f'{i}' if '.' not in i else i for i in args])
        return self

    def change_body(self, body_dict: dict):
        """
        :param body_dict: 字典格式字段
        :return: 返回格式化SQL语句
        """
        key = ','.join(['`' + i + '`' for i in body_dict.keys()])
        value = ['%s' for i in body_dict.keys()]
        insert_sql = f"""INSERT INTO `{self.table}` ({key}) VALUE ({','.join(value)})"""
        return insert_sql

    # 仅供本地调试使用（用完删除）
    def get_raw_sql(self):
        return self.db.cursor._executed

    def insert_id(self):
        return self.db.cursor._result.insert_id

    def sql_execute(self, sql) -> bool:
        """
        :param sql: 可执行的sql语句
        :return: True 发生改变, False 未发生改变
        """
        try:
            ret = self.db.cursor.execute(sql)
        except Exception as e:
            self.db.conn.rollback()
            raise CommonError(message='网络波动')
        return bool(ret)

    @staticmethod
    def resp_data(data: dict):
        if data:
            # 空字段处理
            null_field_dispose(data)
            # 特殊时间字段处理
            time_filed_dispose(data)
        return data

    def limit(self, page, limit):
        """
        :param page: 页数
        :param limit: 条数
        :return:
        """
        skip = (page - 1) * limit
        limit = limit
        self._limit_sql = f'limit {skip}, {limit}'
        return self

    def order_by(self, filed, reverse: bool = False):
        desc = ''
        if reverse:
            desc = 'DESC'
        self._order_by_sql = f'order by {filed} {desc}'
        return self

    def filter(self, filter_sql):
        """
        :param filter_sql: 查询参数
        :return:
        """
        if filter_sql:
            self._where_sql = 'where ' + filter_sql
        return self

    def group_by(self, filed):
        self._group_by = f'group by {filed}'

    def match(self, **kwargs):
        """
        :param kwargs: 查询参数
        :return:
        """
        for value in kwargs.items():
            if isinstance(value[-1], tuple):
                l = []
                l.append(value[0])
                l += (list(value[-1]))
                value = l
            else:
                value = list(value)
                value.append('AND')
            self._match_sql += (tuple(value),)
        return self

    def match_or(self, **kwargs):
        """
        :param match_sql: 查询参数
        :return:
        """
        for value in kwargs.items():
            value += tuple(['OR'])
            self._match_sql += (tuple(value),)
        return self

    # 更换表
    def table_name(self, table_name: str):
        self.table = table_name
        return self


class BaseOperate(BaseSql):

    def start_time(self, start_time: str):
        return f'{self.table}.create_time >= "{start_time}"'

    def end_time(self, end_time: str):
        return f'{self.table}.create_time <= "{end_time}"'

    def handle_match(self):
        for value in self._match_sql:
            self.match_sql += "WHERE %s = %s " % value if not self.match_sql else "AND %s = %s " % value

    # 只取第一条数据
    def first(self, *args):
        sql = f"""select {self._select_args} from {self.table} {self._join_sql} 
        {self._where_sql} {self._group_by} {self._order_by_sql} limit 1"""
        data = self.db.select_one(sql, args)
        self.resp_data(data)
        return data

    # 查看所有
    def all(self, *args):
        if self.pager:
            self.limit(self.pager.page, self.pager.limit)
        sql = f'select {self._select_args} from {self.table} {self._join_sql} {self._where_sql} ' \
              f'{self._group_by} {self._order_by_sql} {self._limit_sql}'
        data_list = self.db.select_many(sql, args)
        for data in data_list:
            self.resp_data(data)
        return data_list

    def get_total(self):
        data = self._total(*self.parma_list)
        return data.get('total')

    # 查看条数
    def _total(self, *args):
        sql = f"""
        select
            ifnull(count(1),0) total
        from
            {self.table}
            {self._join_sql} 
            {self._where_sql}
            {self._group_by}
        """
        return self.db.select_one(sql, args)

    # 新建
    def insert(self, body_dict: dict):
        sql = self.change_body(body_dict)
        ret = self.db.cursor.execute(sql, list(body_dict.values()))
        insert_id = self.insert_id()
        return insert_id

    # 逻辑删除
    def log_delete(self):
        sql = f"""
        update
        {self.table}
        set
        is_del = 1
        {self._where_sql}
        """
        return self.sql_execute(sql)

    # 删除
    def real_delete(self, *args):
        sql = f"""
                delete
                from
                {self.table}
                {self._where_sql}
                """
        return self.db.cursor.execute(sql, args)

    # 插入多条数据
    def insert_many(self, body_list: list):
        """
        :param body_list:
        [{'resume_id': 1, 'path': '/test', 'method': 'GET'},
        {'resume_id': 2, 'path': '/test', 'method': 'GET'},
        {'resume_id': 3, 'path': '/test', 'method': 'GET'}]
        :return:
        """
        if not body_list:
            return False
        key = ','.join(['`' + i + '`' for i in body_list[0].keys()])
        va = ','.join(['%s' for i in range(len(body_list[0].keys()))])
        val = ', '.join([f'({va})' for i in range(len(body_list))])
        val_u = [list(i.values()) for i in body_list]
        value = []
        for i in val_u:
            for j in i:
                value.append(j)
        sql = f"""INSERT INTO {self.table} ({key}) VALUES {val} """
        ret = self.db.cursor.execute(sql, value)
        return ret

    # 更新
    def update(self, body_dict: dict):
        update_filed = ','.join(
            [f"""`{filed}` = %s""" for filed in body_dict]
        )
        update_list = [value if value is None else fr"{value}" for value in body_dict.values()]
        sql = f"""UPDATE {self.table} SET {update_filed} {self._where_sql}"""
        ret = self.db.cursor.execute(sql, update_list + self.parma_list)
        return ret

    def sql_one(self, select_sql: str, *args) -> dict:
        data = self.db.select_one(select_sql, args)
        data = self.resp_data(data)
        return data

    def sql_all(self, select_sql: str, *args) -> list:
        data_list = self.db.select_many(select_sql, args)
        for data in data_list:
            self.resp_data(data)
        return data_list


class SqlHandel:
    """
    Fast processing of sql statement concatenation
    """

    operator_dict = {
        'eq': '=',
        'ne': '!=',
        'gt': '>',
        'ge': '>=',
        'lt': '<',
        'le': '<=',
        'like': 'like',
        'in': 'in',
        'not_in': 'not in',
        'is_null': 'is null',
        'is_not_null': 'is not null',
    }

    def __init__(self):
        """
        __parma_list: Call pymysql as a string formatting parameter dataset
        __base_sql: sql Filtering Criteria
        """
        self.__parma_list = []
        self.__base_sql = ''
        self.__sel_sql = ''
        self.table = ''
        if not self.table:
            table = self.__class__.__name__
            self.table = '_'.join(re.findall('[A-Z][^A-Z]*', table)).lower()

    def set_base_sql(self, base_sql: str, parma: str):
        self.__base_sql = base_sql
        self.__parma_list.append(parma)
        return self

    def get_base_sql(self):
        return self.__base_sql

    def __dict_query(self, arg: dict):
        for key, value in arg.items():
            operator = key.split("__")
            if operator[-1] in self.operator_dict:
                symbol = operator.pop(-1)
            else:
                symbol = 'eq'
            if symbol == 'like':
                value = f'%{value}%'
            if symbol == 'in' or symbol == 'not_in':
                if not isinstance(value, (list, tuple)):
                    raise Exception(f'{symbol} operator value must be list or tuple')
                if len(value) == 1:
                    value = f'({value[0]},)'
                else:
                    value = f'({",".join([str(i) for i in value])})'
                if value == '()':
                    raise Exception(f'{symbol} operator value cannot be empty')
            if (symbol == 'is_null' or symbol == 'is_not_null') and value is not None:
                raise Exception(f'{symbol} operator value must be None')
            operator = '__'.join(operator)

            if value.__class__.__name__ not in ['or_q', 'and_q', 'not_q']:
                value = and_q(value)
            q = value.q

            if symbol == 'is_null' or symbol == 'is_not_null':
                self.__add_where(q, f'`{operator}` {self.operator_dict[symbol]}')
            else:
                self.__add_where(q, f'`{operator}` {self.operator_dict[symbol]} %s')
                if value.__class__.__name__ in ['or_q', 'and_q', 'not_q']:
                    value = value.other
                self.__parma_list.append(value)

    def __add_where(self, q, where_sql: str):
        """
        拼接where条件
        :param q: and_q or or_q or not_q
        :param where_sql: sql where
        """
        if not self.__base_sql:
            self.__base_sql += f'{where_sql}'
        else:
            self.__base_sql += f' {q} {where_sql}'

    def __dict_str(self, arg: str):
        if arg.__class__.__name__ not in ['or_q', 'and_q', 'not_q']:
            arg = and_q(arg)
        q = arg.q
        arg = arg.other
        operator = arg.split("__")
        if operator[-1] in self.operator_dict:
            symbol = operator.pop(-1)
        else:
            symbol = 'eq'
        operator = '__'.join(operator)
        self.__add_where(q, f'`{operator}` {self.operator_dict[symbol]}')

    def query(self, *args):
        for arg in args:
            if isinstance(arg, dict):
                self.__dict_query(arg)

            if isinstance(arg, (str, or_q, and_q, not_q)):
                self.__dict_str(arg)

        return self

    def sql_and(self, *args):
        for i in args:
            parma = i.split('=')
            self.__base_sql = sql_and(self.__base_sql, f"{parma[0]}=%s")
            self.__parma_list.append(parma[1])
        return self

    def __select_sql(self):
        self.__sel_sql = f'select * from {self.table}'
        if self.__base_sql:
            self.__sel_sql = f'select * from {self.table} where {self.__base_sql}'
        return self

    def all(self):
        self.__select_sql()
        print(self.__sel_sql, *self.__parma_list)


if __name__ == '__main__':
    ss = SqlHandel()
    ss.query({'id': 1, 'name__lt': or_q('test'), 'age__in': [1]}, or_q('name__is_null')).all()
    # ss.set_base_sql(sql_and(ss.get_base_sql(), 'ddd=%s'), 'uuo').all()
    # ss.all()

